﻿using Big_Data.Entities;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Big_Data.Data_Base_Excel.Offres_Facteurs_Clés
{
    class Import
    {
        public void ImportSheet1ToDataBase(object[,] val,System.ComponentModel.BackgroundWorker worker,DoWorkEventArgs e)
        {
            // App.config
            int row_country_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["row_country_and_value"]);
            int col_country = Convert.ToInt32(ConfigurationManager.AppSettings["col_country"]);
            int row_date = Convert.ToInt32(ConfigurationManager.AppSettings["row_date"]);
            int col_start_emploi_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_start_emploi_date_and_value"]);
            int col_end_emploi_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_end_emploi_date_and_value"]);
            int col_start_popactive_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_start_popactive_date_and_value"]);

            int id = 0;
            int row_max = val.GetUpperBound(0);
            int col_max = val.GetUpperBound(1);

            Requete requete = new Requete();
            List<string> lst_date = new List<string>();
            List<string> reintialiser = new List<string>(){
                "DELETE FROM EmploiSet",
                "DELETE FROM Population_ActiveSet",
                "DELETE FROM PaysSet",
                //"ALTER TABLE PaysSet ADD Id int IDENTITY(1,1)",
                "DELETE FROM DateSet",
                //"ALTER TABLE DateSet ADD Id int IDENTITY(1,1)"
            };
            string ref_date=string.Empty;
            
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                foreach (var item in reintialiser)
                {
                    entities.Database.ExecuteSqlCommand(item);
                }
                #region associationTest

                //string[] emploi_tab = new string[] { "test", "test1", "test3" };
                //string[] pays_tab = new string[] { "paris", "australie", "allemagne" };
                //List<string> dat = new List<string>() { "1998", "1999", "2000", "1998", "1999", "2000" };
                //dat = dat.Distinct().ToList();
                //int add_date = 0;
                //Date da = null;

                //foreach (var item in dat)
                //{
                //    da = new Date();
                //    da.Année = item.ToString().Trim();
                //    entities.DateSet.Add(da);
                //}
                //entities.SaveChanges();
                
                //for (int k = 0; k < pays_tab.Length; k++)
                //{
                //    Pays pays = new Pays();
                //    pays.Name = pays_tab[k];
                //    int m = 0;
                //    for (int j = 0; j < emploi_tab.Length; j++)
                //    {
                //        Emploi emploi = new Emploi();
                //        emploi.Valeurs = emploi_tab[j];
                //        string test_date = dat[m];
                //        int query = (from dates in entities.DateSet
                //                     where dates.Année.Equals(test_date)
                //                     select dates.Id).FirstOrDefault();
                //        m++;
                //        emploi.DateId = query;
                //        pays.Emploi.Add(emploi);
                //        entities.EmploiSet.Add(emploi);
                //    }
                //    entities.PaysSet.Add(pays);
                //}
                //entities.SaveChanges();

                #endregion
                Pays pays = null;
                Date date = null;

                int cellsMax = (row_max - row_date +1) * col_max;
                int cells = 0,percent=0;

                for (int row = row_date; row <= row_max; row++)
                {
                    bool add = false;
                    int cmpt = 0;
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        worker.ReportProgress(0);
                        return;
                    }
                    for (int col = col_country; col <= col_max; col++)
                    {
                        if (val[row, col] == null)
                        {
                            cells++;
                            percent = (int)Math.Floor((double)100 * cells / cellsMax);
                            worker.ReportProgress(percent);
                            continue;
                        }
                        else
                        {
                            string value = val[row, col].ToString();
                            if (row == row_date && col >= col_start_emploi_date_and_value && col <= col_max)
                            {
                                value = System.Text.RegularExpressions.Regex.Replace(value, "[^0-9.-]", string.Empty);
                                lst_date.Add(value.Trim());
                                if (col == col_max)
                                {
                                    lst_date = lst_date.Distinct().ToList();
                                    foreach (var item in lst_date)
                                    {
                                        date = new Date();
                                        date.Année = item.Trim();
                                        entities.DateSet.Add(date);
                                    }
                                    entities.SaveChanges();
                                }
                            }
                            if (col == col_country && row >= row_country_and_value)
                            {
                                pays = new Pays();
                                pays.Name = value.Trim();
                            }

                            if (col >= col_start_emploi_date_and_value && col <= col_end_emploi_date_and_value && row >= row_country_and_value)
                            {
                                ref_date = lst_date[cmpt];
                                id = requete.GetIdDate(ref_date);
                                cmpt++;
                                Emploi emploi = new Emploi();
                                int indexof = value.IndexOf("..");
                                if (indexof == -1)
                                {
                                    double d = Double.Parse(value);
                                    d = Math.Round(d, 2);
                                    value = d.ToString();
                                }
                                emploi.Valeurs = value.Trim();
                                emploi.DateId = id;
                                pays.Emploi.Add(emploi);
                                entities.EmploiSet.Add(emploi);
                                //add = true;
                            }

                            if (col >= col_start_popactive_date_and_value && col <= col_max && row >= row_country_and_value)
                            {
                                if (col == col_start_popactive_date_and_value)
                                    cmpt = 0;

                                ref_date = lst_date[cmpt];
                                id = requete.GetIdDate(ref_date);
                                cmpt++;
                                Population_Active population = new Population_Active();
                                int indexof = value.IndexOf("..");
                                if (indexof == -1)
                                {
                                    double d = Double.Parse(value);
                                    d = Math.Round(d, 2);
                                    value = d.ToString();
                                }

                                population.Valeurs = value.Trim();
                                population.DateId = id;
                                pays.Population_Active.Add(population);
                                entities.Population_ActiveSet.Add(population);
                                add = true;
                            }

                            cells++;
                            percent = (int)Math.Floor((double)100 * cells / cellsMax);
                            worker.ReportProgress(percent);
                        }
                    }
                    if (add)
                        entities.PaysSet.Add(pays);
                }
                entities.SaveChanges();
            }
        }
    }
}

